import duckdb,pandas, re, os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# 创建 NW_PROJECT 表
def create_table():
    con = get_connection()
    con.execute("""
        CREATE TABLE IF NOT EXISTS NW_PROJECT (
          uuid VARCHAR(32) PRIMARY KEY,
          dwbh VARCHAR(100),
          code VARCHAR(200),
          name VARCHAR(500),
          qzq_type VARCHAR(20) DEFAULT 'KY',
          model VARCHAR(200),
          status CHAR(1) DEFAULT '1',
          create_by VARCHAR(200),
          create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          remarks VARCHAR(500)
        )
    """)
    con.close()

# 获取所有记录
def get_all_data():
    con = get_connection()
    query = "SELECT * FROM NW_PROJECT"
    result = con.execute(query).fetchall()
    con.close()
    return result

# 按条件获取记录
def get_data_by_condition(dwbh, model, qzq_type):
    con = get_connection()
    result = con.execute("SELECT * FROM NW_PROJECT "
                         " WHERE dwbh = ? and model = ? and  qzq_type = ?"
                         "   and status = '1'",
                         (dwbh, model, qzq_type)).fetchall()
    con.close()
    return result


# 根据 ID 获取单条记录
def get_data_by_uuid(uuid):
    con = get_connection()
    query = "SELECT * FROM NW_PROJECT WHERE uuid = ?"
    result = con.execute(query, (uuid,)).fetchone()
    con.close()
    return result

# 添加记录
def add_data(uuid, dwbh, model, code, name, qzq_type, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        INSERT INTO NW_PROJECT (uuid, dwbh, model, code, name, qzq_type, status, create_by, remarks)
        VALUES ( ?, ?, ?, ?, ?, ?, ?,  ?, ?)
    """, (uuid, dwbh, model, code, name, qzq_type, status, create_by, remarks))
    con.close()

# 更新记录
def update_data(uuid, dwbh, model, code, name, qzq_type, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        UPDATE NW_PROJECT
        SET dwbh = ?, model = ?, code = ?, name = ?, qzq_type = ?, status = ?, create_by = ?, remarks = ?
        WHERE uuid = ?
    """, ( dwbh, model, code, name, qzq_type, status, create_by, remarks, uuid))
    con.close()

# 删除记录
def delete_data(uuid):
    con = get_connection()
    con.execute("DELETE FROM NW_PROJECT WHERE uuid = ?", (uuid,))
    con.close()


def get_pagedata(p_dwbh, p_model, p_qzq_type, p_kjnd, p_page = 1, p_pageSize = 10):

    # 计算分页查询的起始位置
    l_offset = (p_page - 1) * p_pageSize

    con = get_connection()
    result = con.execute("SELECT * FROM V_NW_PROJECT_HSM "
                         " WHERE dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type"
                         "   and ( kjnd = $p_kjnd or $p_kjnd is null or kjnd is null) "
                         " ORDER BY code, kjnd, hsm_xmbh"
                         " LIMIT $p_pageSize OFFSET $p_offset ",
                         {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type, 'p_kjnd':p_kjnd, 'p_pageSize':p_pageSize, 'p_offset':l_offset}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    # 获取数据总条数
    total_count = con.execute("SELECT COUNT(*) FROM V_NW_PROJECT_HSM "
                         " WHERE dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type"
                         "   and ( kjnd = $p_kjnd or $p_kjnd is null or kjnd is null) ",
                              {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type, 'p_kjnd':p_kjnd}).fetchone()[0]

    con.close()
    return [data, total_count]



def get_xhlist(p_dwbh, p_model, p_qzq_type, p_kjnd):


    con = get_connection()
    result = con.execute("SELECT distinct code, name FROM V_NW_PROJECT_HSM "
                         " WHERE dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type"
                         "   and ( kjnd = $p_kjnd or $p_kjnd is null or kjnd is null) "
                         " ORDER BY code, kjnd, hsm_xmbh",
                         {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type, 'p_kjnd':p_kjnd}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    con.close()
    return data

# 从excel导入数据
def load_file(p_dwbh, p_model, p_qzq_type, p_filepath):
    try:
        excel_data = pandas.read_excel(p_filepath, dtype=str)
        # df1 = excel_data.get('Sheet1')
        df1 = excel_data
        con = get_connection()
        con.register('temp_nw_project', df1)

        # 先删除子表，再删除主表
        con.execute("""
            DELETE FROM NW_PROJECT_HSM
             where project_uuid in (
            select uuid from NW_PROJECT
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code in (
                select 型号编号
                  from temp_nw_project
               ));
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        con.execute("""
            DELETE FROM NW_PROJECT
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code in (
                select 型号编号
                  from temp_nw_project
               );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        # 再插入数据，主表
        con.execute("""
            INSERT INTO NW_PROJECT(uuid, dwbh, model, qzq_type, code, name, create_by, remarks)
            select uuid(), dwbh, model, qzq_type, 型号编号, 型号名称, create_by, 备注
              from (
                select distinct $p_dwbh as dwbh, 
                   COALESCE(模型, $p_model) as model, 
                   COALESCE(类型, $p_qzq_type, '科研') as qzq_type, 
                   型号编号,
                   型号名称,
                   'admin' as create_by,
                   备注
              from temp_nw_project
              );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        # 再插入数据，子表
        con.execute("""
            INSERT INTO NW_PROJECT_HSM(uuid, dwbh, model, qzq_type, 
                project_uuid, kjnd, hsm_xmbh, calc, create_by, remarks)
            select uuid(), $p_dwbh, COALESCE(模型, $p_model), COALESCE(类型, $p_qzq_type, '科研'), 
                   p.uuid,
                   会计年度,
                   主业项目编号,
                   case when 计算方式 = '-' then '减'  
                        when 计算方式 = '减' then '减'  
                        else '加'
                    end as 计算方式,
                   'admin',
                   备注
              from NW_PROJECT p, temp_nw_project
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code = 型号编号
              ;
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        con.close()
        return 200
    except Exception as e:
        return 400

def is_valid_year(year_str):
    # print(year_str)
    # 使用正则表达式匹配四位数字
    try:
        year_str = str(year_str) if year_str is not None else ''
        return bool(re.match(r'^\d{4}$', year_str))
    except Exception as e:
        print(e)
        return False


# 从excel导入数据-新格式
def load_file_new(p_dwbh, p_model, p_qzq_type, p_filepath):
    try:
        excel_data = pandas.read_excel(p_filepath, dtype=str)
        # df1 = excel_data.get('Sheet1')
        df1 = excel_data
        con = get_connection()
        con.register('temp_nw_project', df1)

        # 先删除子表，再删除主表
        con.execute("""
            DELETE FROM NW_PROJECT_HSM
             where project_uuid in (
            select uuid from NW_PROJECT
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code in (
                select 型号编号
                  from temp_nw_project
               ));
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        con.execute("""
            DELETE FROM NW_PROJECT
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code in (
                select 型号编号
                  from temp_nw_project
               );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        con.execute("""
            DELETE FROM NW_PROJECT_DESC
             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
               and code in (
                select 型号编号
                  from temp_nw_project
               );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        # 再插入数据，原表
        con.execute("""
            INSERT INTO NW_PROJECT_DESC(uuid, dwbh, model, qzq_type, code, name, kjnd, hsm_desc, hsm_desc2, create_by, remarks)
            select uuid(), dwbh, model, qzq_type, 型号编号, 型号名称, 会计年度, hsm_desc, hsm_desc2, create_by, 备注
              from (
                select distinct $p_dwbh as dwbh, 
                   COALESCE(模型, $p_model) as model, 
                   COALESCE(类型, $p_qzq_type, '科研') as qzq_type, 
                   型号编号,
                   型号名称,
                   会计年度,
                   主业项目编号 as hsm_desc,
                   "主业项目编号（减）" as hsm_desc2,
                   'admin' as create_by,
                   备注
              from temp_nw_project
              );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})
        # 再插入数据，主表
        con.execute("""
            INSERT INTO NW_PROJECT(uuid, dwbh, model, qzq_type, code, name, create_by, remarks)
            select uuid(), dwbh, model, qzq_type, 型号编号, 型号名称, create_by, 备注
              from (
                select distinct $p_dwbh as dwbh, 
                   COALESCE(模型, $p_model) as model, 
                   COALESCE(类型, $p_qzq_type, '科研') as qzq_type, 
                   型号编号,
                   型号名称,
                   'admin' as create_by,
                   备注
              from temp_nw_project
              );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type})

        # 遍历每一行
        for index, row in df1.iterrows():
            # 每一行是一个 pandas Series 对象，可以通过列名访问每一列的值
            # print(f"Index: {index}")
            # print(row)  # 打印整行数据
            # 可以按列名获取特定列的值
            r_model = row['模型']
            r_qzq_type = row['类型']
            r_code = row['型号编号']
            r_name = row['型号名称']
            r_kjnd = row['会计年度']
            r_hsm_xmbh = row['主业项目编号']
            r_hsm_xmbh_sub = row['主业项目编号（减）']
            r_remarks = row['备注']

            # 解析起始和结束年份
            if not pandas.isna(r_kjnd):
                start_year = r_kjnd
                end_year = r_kjnd

                if r_kjnd.find('-') != -1:
                    start_year, end_year = map(int, r_kjnd.split('-'))
                # print(start_year, end_year)
                if not is_valid_year(start_year):
                    return 402
                for year in range(int(start_year), int(end_year) + 1):
                    if not pandas.isna(r_hsm_xmbh):
                        l_calc = '加'
                    else:
                        l_calc = '减'
                        r_hsm_xmbh = r_hsm_xmbh_sub
                    if pandas.isna(r_hsm_xmbh):
                        return 401 ## 缺少项目编号
                    ## 插入一年、一个核算项目编号的数据
                    for one_xmbh in r_hsm_xmbh.split(','):
                        con.execute("""
                            INSERT INTO NW_PROJECT_HSM(uuid, dwbh, model, qzq_type, 
                                project_uuid, kjnd, hsm_xmbh, calc, create_by, remarks)
                            select uuid(), $p_dwbh, COALESCE($r_model, $p_model), COALESCE($r_qzq_type, $p_qzq_type, '科研'), 
                                   p.uuid,
                                   $year,
                                   $xmbh,
                                   $l_calc as 计算方式,
                                   'admin',
                                   $r_remarks
                              from NW_PROJECT p
                             where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
                               and code = $r_code
                              ;
                        """, {'p_dwbh': p_dwbh, 'r_model':r_model, 'p_model': p_model, 'r_qzq_type':r_qzq_type, 'p_qzq_type': p_qzq_type,
                              'year':year, 'xmbh':one_xmbh, 'l_calc':l_calc, 'r_remarks':r_remarks, 'r_code':r_code})
            else:
                year = None

                if not pandas.isna(r_hsm_xmbh):
                    l_calc = '加'
                else:
                    l_calc = '减'
                    r_hsm_xmbh = r_hsm_xmbh_sub
                if pandas.isna(r_hsm_xmbh):
                    return 401  ## 缺少项目编号
                ## 插入一年、一个核算项目编号的数据
                for one_xmbh in r_hsm_xmbh.split(','):
                    con.execute("""
                        INSERT INTO NW_PROJECT_HSM(uuid, dwbh, model, qzq_type, 
                            project_uuid, kjnd, hsm_xmbh, calc, create_by, remarks)
                        select uuid(), $p_dwbh, COALESCE($r_model, $p_model), COALESCE($r_qzq_type, $p_qzq_type, '科研'), 
                               p.uuid,
                               $year,
                               $xmbh,
                               $l_calc as 计算方式,
                               'admin',
                               $r_remarks
                          from NW_PROJECT p
                         where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
                           and code = $r_code
                          ;
                    """, {'p_dwbh': p_dwbh, 'r_model': r_model, 'p_model': p_model, 'r_qzq_type': r_qzq_type,
                          'p_qzq_type': p_qzq_type,
                          'year': year, 'xmbh': one_xmbh, 'l_calc': l_calc, 'r_remarks': r_remarks, 'r_code': r_code})



        con.close()
        return 200
    except Exception as e:
        # print(e)
        return 400
# 删除记录
def clear_data(p_dwbh, p_model, l_qzq_type):
    con = get_connection()
    # print("clear_data:", p_dwbh, p_model, l_qzq_type)
    # 清空型号核算关系配置明细
    con.execute("""
        DELETE FROM NW_PROJECT_HSM
         where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
         """,
                {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': l_qzq_type})
    # 清空型号配置表
    con.execute("""
        DELETE FROM NW_PROJECT
         where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
         """,
                {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': l_qzq_type})
    # 清空型号配置表-原表
    con.execute("""
        DELETE FROM NW_PROJECT_DESC
         where dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
         """,
                {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': l_qzq_type})
    con.close()



